package com.youme.tools.cg.db;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.jsp.jstl.sql.Result;

import com.youme.tools.cg.db.enums.OracleDataType;
import com.youme.tools.cg.db.util.ConnHelper;
import com.youme.tools.cg.db.util.JdbcHelper;
import com.youme.tools.cg.pojo.FieldBean;
import com.youme.tools.cg.pojo.TableBean;

public class OracleReader implements DbReader {

  @SuppressWarnings("unused")
  @Override
  public List<TableBean> readTable(List<String> tableNames) throws SQLException {
    List<TableBean> list = new ArrayList<>();
    Result result = null;
    Connection conn = null;
    conn = ConnHelper.getJDBCConn();

    String sql =
        "SELECT T.TABLE_NAME, C.COMMENTS FROM USER_TABLES T, USER_TAB_COMMENTS C WHERE C.TABLE_NAME = T.TABLE_NAME ";
    if (null != tableNames && !tableNames.isEmpty()) {
      StringBuilder tableNameInSql = new StringBuilder(" AND T.TABLE_NAME IN (");
      for (String name : tableNames) {
        tableNameInSql.append(" ?,");
      }
      tableNameInSql.setCharAt(tableNameInSql.length() - 1, ' ');
      tableNameInSql.append(")");
      sql += tableNameInSql.toString();
    }

    List<String> params = new ArrayList<String>();
    if (null != tableNames && !tableNames.isEmpty()) {
      for (String name : tableNames) {
        params.add(name);
      }
    }
    result = JdbcHelper.executeQuery(conn, sql, params, false);
    if (null != result && 0 < result.getRowCount()) {
      for (int i = 0; i < result.getRowCount(); i++) {
        Map<?, ?> map = result.getRows()[i];
        list.add(loadTableInfo(map));
      }
    }
    JdbcHelper.closeAll(conn, null, null, null);
    if (null != list && !list.isEmpty()) {
      for (TableBean table : list) {
        conn = ConnHelper.getJDBCConn();
        // 字段信息开始
        List<FieldBean> fields = new ArrayList<>();
        sql =
            "SELECT t.COLUMN_NAME, t.DATA_TYPE, c.COMMENTS FROM user_tab_columns t, user_col_comments c "
                + " WHERE t.table_name = c.table_name AND t.column_name = c.column_name AND t.table_name = ?";
        params.clear();
        params.add(table.getOriginalName());
        result = JdbcHelper.executeQuery(conn, sql, params, false);
        if (null != result && 0 < result.getRowCount()) {
          for (int i = 0; i < result.getRowCount(); i++) {
            Map<?, ?> map = result.getRows()[i];
            fields.add(loadFieldInfo(map));
          }
        }
        // 字段信息结束

        // 主键信息开始
        List<FieldBean> pks = new ArrayList<>();
        sql = "SELECT t.COLUMN_NAME FROM user_cons_columns t, user_constraints c "
            + " WHERE t.constraint_name = c.constraint_name  AND c.constraint_type = 'P' AND c.table_name = ?";
        result = JdbcHelper.executeQuery(conn, sql, params, false);
        if (null != result && 0 < result.getRowCount()) {
          for (int i = 0; i < result.getRowCount(); i++) {
            Map<?, ?> map = result.getRows()[i];
            pks.add(loadFieldInfo(map));
          }
        }
        JdbcHelper.closeAll(conn, null, null, null);
        // 主键信息结束
        convertFileds(fields, pks);
        table.setFields(fields);
        table.setPks(pks);
      }
    }
    return list;
  }

  @Override
  public String convertDataType(Object obj) {
    String dbType = obj.toString();
    return OracleDataType.getJavaTypeByDbType(dbType);
  }

  @Override
  public boolean isPK(Object obj) {
    return false;
  }

  /**
   * 读取表信息
   * 
   * @param map
   * @return 表信息
   */
  private TableBean loadTableInfo(Map<?, ?> map) {
    TableBean item = new TableBean();
    String tableName = map.get("TABLE_NAME").toString();
    item.setOriginalName(tableName);
    if (tableName.charAt(1) == '_') {
      tableName = tableName.substring(2);
    }
    item.setName(tableName);
    if (null != map.get("COMMENTS")) {
      item.setDescription(map.get("COMMENTS").toString());
    } else {
      item.setDescription("");
    }
    return item;
  }

  /**
   * 读取字段信息
   * 
   * @param map
   * @return 字段信息
   */
  private FieldBean loadFieldInfo(Map<?, ?> map) {
    FieldBean item = new FieldBean();
    item.setName(map.get("COLUMN_NAME").toString());
    if (null != map.get("DATA_TYPE")) {
      item.setDataType(convertDataType(map.get("DATA_TYPE")));
    }
    item.setIsPK(isPK(""));
    if (null != map.get("COMMENTS")) {
      item.setDescription(map.get("COMMENTS").toString());
    } else {
      item.setDescription("");
    }
    return item;
  }

  /**
   * 数据重载
   * 
   * @Title: convertFileds
   * @Description: 数据重载
   * @param fields 字段信息
   * @param pks 主键信息
   */
  private void convertFileds(List<FieldBean> fields, List<FieldBean> pks) {
    for (FieldBean field : fields) {
      String fieldName = field.getName();
      for (FieldBean pk : pks) {
        String pkName = pk.getName();
        if (pkName.equals(fieldName)) {
          field.setIsPK(true);
          pk.setDataType(field.getDataType());
          pk.setDescription(field.getDescription());
          pk.setIsPK(true);
        }
      }
    }
  }

}
